package com.jy.datapipeline.export.dao.impl;

import com.jy.datapipeline.common.constants.ExpConstants;
import com.jy.datapipeline.common.exception.GlobalException;
import com.jy.datapipeline.common.util.AssertUtil;
import com.jy.datapipeline.common.util.RandomNumsUtil;
import com.jy.datapipeline.export.common.constants.SplitKeyExecuteConstant;
import com.jy.datapipeline.export.common.enums.impl.QueryTypeEnums;
import com.jy.datapipeline.export.entity.ExpRuleDetailModel;
import com.jy.datapipeline.export.dao.ExpRuleDetailDao;

import java.math.BigInteger;
import java.sql.*;
import java.util.*;
import java.util.Date;

import com.jy.datapipeline.export.splitkey.utils.SqlUtil;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.jdbc.core.namedparam.SqlParameterSourceUtils;
import org.springframework.stereotype.Repository;

import javax.sql.DataSource;

@Slf4j
@Repository
public class ExpRuleDetailDaoImpl implements ExpRuleDetailDao {
    @Autowired
    private NamedParameterJdbcTemplate namedParameterJdbcTemplate;

    @Override
    public List<Map<String, Object>> findExpRuleDetailByList(String ruleId,String schemaName, String ruleTbl,
        int iDisplayStart, int iDisplayLength) {

        List<Map<String, Object>> list=new ArrayList<>();
        try {
            StringBuffer sb=new StringBuffer();
            Map<String,Object> params = new HashMap<String,Object>();
            sb.append("SELECT a.id,a.schema_name,a.rule_id,a.rule_detail_id,a.rule_tbl_suffix,a.rule_tbl_type,"
                + " a.rule_tbl,a.col_list,a.rule_type,a.row_range,a.init_row,a.data_period,a.data_unit "
                + " ,DATE_FORMAT(a.update_time,'%Y-%m-%d %H:%i:%s') as update_time,a.fun_value,a.split_key from exp_rule_detail a   "
                + " where 1=1  ");
            if(ruleId!=null && !ruleId.equals("")){
                sb.append(" and a.rule_id =:ruleId");
                params.put("ruleId",ruleId);
            }
            if(schemaName!=null && !schemaName.equals("")){
                sb.append(" and a.schema_name like :schemaName");
                params.put("schemaName","%"+schemaName+"%");
            }
            if(ruleTbl!=null && !ruleTbl.equals("")){
                sb.append(" and a.rule_tbl like :ruleTbl ");
                params.put("ruleTbl","%"+ruleTbl+"%");
            }
            sb.append(" order by a.update_time desc  ");
            if(iDisplayStart>-1 && iDisplayLength>-1){
                sb.append("LIMIT  :iDisplayStart ,:iDisplayLength ;");
                params.put("iDisplayStart",iDisplayStart);
                params.put("iDisplayLength",iDisplayLength);
            }

            list=this.namedParameterJdbcTemplate.queryForList(sb.toString(),params);
        }catch (Exception e){
            log.error("findDataStoreInfoByList exception",e);
        }

        return list;
    }

    @Override
    public int findExpRuleDetailByCount(String ruleId,String schemaName, String ruleTbl) {
        int count=0;
        List<Map<String, Object>> list;
        try {
            StringBuffer sb=new StringBuffer();
            Map<String,Object> params = new HashMap<String,Object>();
            sb.append("SELECT  COUNT(a.id) as count from exp_rule_detail a   "
                + " where 1=1  ");
            if(ruleId!=null && !ruleId.equals("")){
                sb.append(" and a.rule_id =:ruleId");
                params.put("ruleId",ruleId);
            }
            if(schemaName!=null && !schemaName.equals("")){
                sb.append(" and a.schema_name like :schemaName");
                params.put("schemaName","%"+schemaName+"%");
            }
            if(ruleTbl!=null && !ruleTbl.equals("")){
                sb.append(" and a.rule_tbl like :ruleTbl ");
                params.put("ruleTbl","%"+ruleTbl+"%");
            }
            sb.append(" order by a.update_time desc  ");


            list=this.namedParameterJdbcTemplate.queryForList(sb.toString(),params);
            if(list.size()>0){
                count=Integer.parseInt(list.get(0).get("count")!=null?list.get(0).get("count").toString():"0");
            }
        }catch (Exception e){
            log.error("findExpRuleDetailByCount exception",e);
        }

        return count;
    }

    @Override
    public boolean updateExpRuleDetail(ExpRuleDetailModel erdm) {
        Map<String,Object> params=new HashMap<>();
        try {
            String sql = "update exp_rule_detail set schema_name=:schema_name,rule_tbl=:rule_tbl,"
                + " col_list=:col_list,rule_type=:rule_type,row_range=:row_range,update_time=NOW(),"
                + " rule_tbl_suffix=:rule_tbl_suffix,rule_tbl_type=:rule_tbl_type,init_row=:init_row,"
                + " data_unit=:data_unit,data_period=:data_period,"
                + " fun_value=:fun_value,split_key=:split_key"
                + " where rule_detail_id=:rule_detail_id";
            params.put("schema_name", erdm.getSchemaName());
            params.put("rule_tbl", erdm.getRuleTbl());
            params.put("col_list", erdm.getColList());
            params.put("rule_type", erdm.getRuleTYpe());
            params.put("row_range", erdm.getRowRange());
            params.put("rule_tbl_suffix",erdm.getRuleTblSuffix());
            params.put("rule_tbl_type",erdm.getRuleTblType());
            params.put("init_row",erdm.getInitRow());
            params.put("rule_detail_id", erdm.getRuleDetailId());
            params.put("data_unit",erdm.getDataUnit());
            params.put("data_period",erdm.getDataPeriod());
            params.put("fun_value",erdm.getFunValue());
            params.put("split_key",erdm.getSplitKey());
            int req = this.namedParameterJdbcTemplate.update(sql,params);
            return req>0?true:false;
        }catch (Exception e){
            log.error("error",e);
        }

        return  false;
    }

    public boolean updateExpRuleDetailIsAccurate(ExpRuleDetailModel erdm) {
        Map<String,Object> params=new HashMap<>();
        try {
            String sql = "update exp_rule_detail set is_accurate=:is_accurate, update_time = NOW() where rule_detail_id=:rule_detail_id";
            params.put("is_accurate",erdm.getIsAccurate());
            params.put("rule_detail_id",erdm.getRuleDetailId());
            return this.namedParameterJdbcTemplate.update(sql,params) > 0;
        }catch (Exception e){
            log.error("error",e);
        }
        return  false;
    }

    @Override
    public boolean deleteByRuleId(String ruleId) {
        Map<String,Object> params=new HashMap<>();
        try {
            String sql = "delete from  exp_rule_detail "
                + " where rule_id=:ruleId";
            params.put("rule_id", ruleId);
            int req = this.namedParameterJdbcTemplate.update(sql, params);
            return req>0?true:false;
        }catch (Exception e){
            log.error("deleteByRuleId",e);
        }
        return  false;
    }

    @Override
    public void batchSave(List<ExpRuleDetailModel> expRuleDetailModelList) throws Exception {
        List<Object> params = new ArrayList<>();
        Map<String, Object> param;
        for (ExpRuleDetailModel expRuleDetailModel : expRuleDetailModelList) {
            param = new HashMap<>();
            param.put("colList", expRuleDetailModel.getColList());
            param.put("rowRange", expRuleDetailModel.getRowRange());
            param.put("ruleId", expRuleDetailModel.getRuleId());
            param.put("ruleTYpe", expRuleDetailModel.getRuleTYpe());
            param.put("ruleTbl", expRuleDetailModel.getRuleTbl());
            param.put("schemaName", expRuleDetailModel.getSchemaName());
            param.put("update_time", expRuleDetailModel.getUpdate_time());
            param.put("ruleDetailId", expRuleDetailModel.getRuleDetailId());
            param.put("ruleTblSuffix", expRuleDetailModel.getRuleTblSuffix());
            param.put("ruleTblType", expRuleDetailModel.getRuleTblType());
            param.put("initRow", expRuleDetailModel.getInitRow());
            param.put("dataPeriod", expRuleDetailModel.getDataPeriod());
            param.put("dataUnit", expRuleDetailModel.getDataUnit());
            param.put("funValue", expRuleDetailModel.getFunValue());
            param.put("splitKey", expRuleDetailModel.getSplitKey());
            params.add(param);
        }
        String sql = "INSERT INTO exp_rule_detail (col_list, row_range, rule_id, rule_type, rule_tbl, schema_name, update_time, rule_detail_id" +
                ", rule_tbl_suffix, rule_tbl_type, init_row, data_period, data_unit, fun_value, split_key) " +
                "VALUES (:colList, :rowRange, :ruleId, :ruleTYpe, :ruleTbl, :schemaName, :update_time, :ruleDetailId, :ruleTblSuffix, :ruleTblType, :initRow, :dataPeriod, :dataUnit, :funValue, :splitKey)";
        int[] ints = namedParameterJdbcTemplate.batchUpdate(sql, SqlParameterSourceUtils.createBatch(params));
        AssertUtil.totalAffectedRows(ints.length, expRuleDetailModelList, ExpConstants.BATCH_ADD_FAIL);
    }

    @Override
    public void batchDelete(Set<String> ruleIds) throws Exception {
        List<Object> params = new ArrayList<>();
        Map<String, Object> param;
        for (String ruleId : ruleIds) {
            param = new HashMap<>();
            param.put("ruleId", ruleId);
            params.add(param);
        }
        String sql = "DELETE FROM exp_rule_detail WHERE rule_id = :ruleId";
        int[] ints = namedParameterJdbcTemplate.batchUpdate(sql, SqlParameterSourceUtils.createBatch(params));
        AssertUtil.totalAffectedRows(ints.length, ruleIds, ExpConstants.BATCH_DELETE_FAIL);
    }

    private Connection getConnection(DataSource dataSource) throws SQLException {
        Connection connection = dataSource.getConnection();
        connection.setAutoCommit(false);
        return connection;
    }

    @Override
    public BigInteger getCount(BigInteger leftMin, BigInteger rightMax,
                               ExpRuleDetailModel expRuleDetailModel, String ruleTbl, DataSource dataSource) {
        String sql = SqlUtil.QUERY_COUNT_SQL
                .replace(SqlUtil.SCHEMA, expRuleDetailModel.getSchemaName())
                .replace(SqlUtil.TABLE, ruleTbl)
                .replace(SqlUtil.SPLIT_KEY, expRuleDetailModel.getSplitKey())
                .replace(SqlUtil.MIN, leftMin.toString())
                .replace(SqlUtil.MAX, rightMax.toString());
        try (Connection connection = this.getConnection(dataSource);
             PreparedStatement preparedStatement = connection.prepareStatement(sql);
             ResultSet resultSet = preparedStatement.executeQuery()) {
            resultSet.next();
            int count = resultSet.getInt(1);
            log.info("{}", count);
            return BigInteger.valueOf(count);
        } catch (Exception e) {
            e.printStackTrace();
        }
        throw new GlobalException(ExpConstants.RANGE_QUERY_COUNT_EXP);
    }

    public Map<String, Object> getSplitKey(ExpRuleDetailModel expRuleDetailModel, String ruleTbl,
                                               Date dataDate, DataSource dataSource) throws SQLException {
        Map<String, Object> res = new HashMap<>();

        String selectMaxSql = "select max(" + expRuleDetailModel.getSplitKey() + ")  , min(" + expRuleDetailModel.getSplitKey() +
                ") from " + expRuleDetailModel.getSchemaName() + "." + ruleTbl ;

        if(expRuleDetailModel.getRuleTYpe()==3){
            selectMaxSql= "select max(" + expRuleDetailModel.getSplitKey() + ")  , min(" + expRuleDetailModel.getSplitKey() +
                    ") from (" +  RandomNumsUtil.paramsToStr(expRuleDetailModel.getRowRange(), dataDate)+")";
        }

        Object MaxSplitPK = null;
        Object MinSplitPK = null;

        try (Connection connection = this.getConnection(dataSource);
             PreparedStatement preparedStatement = connection.prepareStatement(selectMaxSql);
             ResultSet resultSet = preparedStatement.executeQuery()) {
            ResultSetMetaData metaData = resultSet.getMetaData();
            int splitPKType = metaData.getColumnType(1);

            if (resultSet.next()) {
                MaxSplitPK = resultSet.getObject(1);
                MinSplitPK = resultSet.getObject(2);
            }
            log.info("splitSql:{},MaxSplitPK:{},MinSplitPK:{},MinSplitPKType:{}", selectMaxSql, MaxSplitPK, MinSplitPK, splitPKType);

            res.put(SplitKeyExecuteConstant.MAX_SPLIT_KEY, MaxSplitPK);
            res.put(SplitKeyExecuteConstant.MIN_SPLIT_KEY, MinSplitPK);
            res.put(SplitKeyExecuteConstant.SPLIT_KEY_TYPE, splitPKType);
        }
        return res;
    }

    @Override
    public BigInteger getTotalCount(ExpRuleDetailModel expRuleDetailModel, String ruleTbl, DataSource dataSource, Date dataDate) {
        String sql="select count(1) as count from " +expRuleDetailModel.getSchemaName() + "." + ruleTbl ;
        if(expRuleDetailModel.getRuleTYpe() == QueryTypeEnums.CUSTOM_QUERY.getCode()){
            sql="select count(1) as count from ("+  RandomNumsUtil.paramsToStr(expRuleDetailModel.getRowRange(), dataDate)+")";
        }
        log.info("max count sql :{}",sql);

        int count = 0;

        try (Connection connection = dataSource.getConnection();
             PreparedStatement preparedStatement = connection.prepareStatement(sql);
             ResultSet resultSet=preparedStatement.executeQuery()) {
            if (resultSet != null && resultSet.next()) {
                count = resultSet.getInt(1);
                log.info(" count:{}",count);
            }
        } catch (SQLException e) {
            log.error("getMaxCountByTable error", e);
        }
        return BigInteger.valueOf(count);
    }
}
